/*
 * Copyright 2012 ITC Tsinghua University. All Rights Reserved.
 */
package cn.edu.tsinghua.cic.ccss.bo.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.forten.core.dao.rdb.hibernate.HibernateDao;
import org.forten.core.dao.rdb.hibernate.QueryInfo;
import org.forten.core.dao.rdb.query.hql.expression.Between;
import org.forten.core.dao.rdb.query.hql.expression.Compare;
import org.forten.core.dao.rdb.query.hql.expression.Expression;
import org.forten.core.dao.rdb.query.hql.expression.In;
import org.forten.core.dao.rdb.query.hql.expression.Like;
import org.forten.core.dao.rdb.query.hql.order.Order;
import org.forten.core.dao.rdb.query.hql.statement.OrderBy;
import org.forten.core.dao.rdb.query.hql.statement.Select;
import org.forten.core.dao.rdb.query.parameter.Param;
import org.forten.core.dao.rdb.query.parameter.Type;
import org.forten.core.qo.QueryField.MatchType;
import org.forten.core.ro.DtoResultWithPageInfo;
import org.forten.core.ro.PageInfo;
import org.forten.utils.collection.CollectionUtil;
import org.forten.utils.common.DateUtil;
import org.forten.utils.common.NumberUtil;
import org.forten.utils.common.StringUtil;
import org.forten.utils.security.MD5Util;
import org.forten.utils.security.TripletsDESUtil;
import org.forten.utils.system.BeanPropertyUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo;
import cn.edu.tsinghua.cic.ccss.bo.SystemSettingBo;
import cn.edu.tsinghua.cic.ccss.dto.CadreDto;
import cn.edu.tsinghua.cic.ccss.dto.CourseCanceledDto;
import cn.edu.tsinghua.cic.ccss.dto.CourseDto;
import cn.edu.tsinghua.cic.ccss.dto.CourseSelectionDto;
import cn.edu.tsinghua.cic.ccss.dto.MsgResultOfChekInDto;
import cn.edu.tsinghua.cic.ccss.dto.SelectedCourseDto4Export;
import cn.edu.tsinghua.cic.ccss.dto.SuperCadreDto;
import cn.edu.tsinghua.cic.ccss.entity.Cadre;
import cn.edu.tsinghua.cic.ccss.entity.Course;
import cn.edu.tsinghua.cic.ccss.entity.CourseCanceled;
import cn.edu.tsinghua.cic.ccss.entity.CourseSelection;
import cn.edu.tsinghua.cic.ccss.entity.MsgResultOfChekIn;
import cn.edu.tsinghua.cic.ccss.qo.CourseSelectCadreQo;
import cn.edu.tsinghua.cic.ccss.qo.CourseSelectionQo;
import cn.edu.tsinghua.cic.ccss.qo.MsgResultOfChekInQo;

/**
 * 
 * 
 * @author <a href="mailto:du_yi@bbn.cn">Duyi</a>
 * 
 * @since 2012-5-15
 */
@Service("courseSelectBo")
public class CourseSelectBoImpl implements CourseSelectBo {
	@Resource
	private HibernateDao hibernateDao;
	@Resource(name = "systemSettingBo")
	private SystemSettingBo systemSettingBo;

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#selectCourse4Admin(java.lang
	 * .Long, java.lang.String, java.lang.Long[])
	 */
	@Override
	@Transactional
	public void selectCourse4Admin(Long courseId, String optUserNum,
			Long... cadreIds) {
		Course course = hibernateDao.load(Course.class, courseId);
		int credit = course.getCredit();
		Calendar teachTime = Calendar.getInstance();
		teachTime.setTime(course.getTeachTime());
		Calendar currentTime = Calendar.getInstance();
		currentTime.setTime(new Date());

		List<CourseSelection> list = new ArrayList<CourseSelection>(
				cadreIds.length);
		for (Long cid : cadreIds) {
			if (isSelected(courseId, cid))
				continue;
			if (currentTime.before(teachTime)
					&& currentTime.get(Calendar.MONTH) == teachTime
							.get(Calendar.MONTH)) {
				String hql = "update Cadre SET currentMonthTotalCredit=currentMonthTotalCredit+:credit where id=:id";
				hibernateDao.executeUpdate(hql,
						Param.getCommonParam("credit", credit, Type.INTEGER),
						Param.getCommonParam("id", cid, Type.LONG));
			} else if (currentTime.before(teachTime)
					&& currentTime.get(Calendar.MONTH) != teachTime
							.get(Calendar.MONTH)) {
				String hql = "update Cadre SET nextMonthTotalCredit=nextMonthTotalCredit+:credit where id=:id";
				hibernateDao.executeUpdate(hql,
						Param.getCommonParam("credit", credit, Type.INTEGER),
						Param.getCommonParam("id", cid, Type.LONG));
			}
			CourseSelection cs = new CourseSelection();
			cs.setCadreId(cid);
			cs.setCourseId(courseId);
			cs.setSelectCardId(optUserNum);
			cs.setSelectTime(currentTime.getTime());
			list.add(cs);
		}

		// for (CourseSelection cs : list) {
		// String hql =
		// "delete from CourseCanceled where cadreId=:id1 and courseId=:id2";
		// hibernateDao.executeUpdate(hql,
		// Param.getCommonParam("id1", cs.getCadreId(), Type.LONG),
		// Param.getCommonParam("id2", cs.getCourseId(), Type.LONG));
		// }

		hibernateDao.save(list.toArray(new CourseSelection[list.size()]));
		// 更新课程的选课人数
		String hql = "UPDATE Course SET classChosenNum=(classChosenNum+:num) WHERE id=:id";
		hibernateDao.executeUpdate(hql,
				Param.getCommonParam("num", list.size(), Type.INTEGER),
				Param.getCommonParam("id", courseId, Type.LONG));
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#queryStudentsForThisCourse
	 * (java.lang.Long)
	 */
	@Override
	@Transactional
	public List<CourseSelectionDto> queryStudentsForThisCourse(Long courseId) {
		String hql = "FROM CourseSelection WHERE courseId=:id AND deletedCardNum='0'";
		List<CourseSelection> entities = hibernateDao.findEntities(hql, true,
				Param.getCommonParam("id", courseId, Type.LONG));
		List<CourseSelectionDto> dtoes = new ArrayList<CourseSelectionDto>(
				entities.size());
		for (CourseSelection entity : entities) {
			dtoes.add(getCourseSelectionDtoFromEntity(entity));
		}
		return dtoes;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#isOverMaxCredit(java.lang.
	 * Long, java.lang.Long)
	 */
	@Override
	@Transactional
	public boolean isOverMaxCredit(Long cadreId, Long courseId) {
		// 获得要选课程的学分和开课时间
		String hql = "SELECT credit,teachTime FROM Course WHERE id=:courseId";
		Object[] result = hibernateDao.findCommonObject(hql,
				Param.getCommonParam("courseId", courseId, Type.LONG));
		int credit = (Integer) result[0];
		Calendar teachTime = Calendar.getInstance();
		teachTime.setTime((Date) result[1]);
		Calendar currentTime = Calendar.getInstance();
		currentTime.setTime(new Date());

		// 如果开课在当月查询currentMonthTotalCredit，如果在下月查询nextMonthTotalCredit
		if (currentTime.before(teachTime)
				&& currentTime.get(Calendar.MONTH) == teachTime
						.get(Calendar.MONTH)) {
			hql = "select isNewFlag,jobLevel,currentMonthTotalCredit from Cadre where id=:cadreId";
			Object[] cadreInfo = hibernateDao.findCommonObject(hql,
					Param.getCommonParam("cadreId", cadreId, Type.LONG));
			String isNewFlag = cadreInfo[0].toString();
			String jobLevel = TripletsDESUtil.decrypt(
					systemSettingBo.getStringValue("CURRENT_TRIPLETS_DES_KEY"),
					cadreInfo[1].toString());
			int currentMonthTotalCredit = (Integer) cadreInfo[2];
			int total = currentMonthTotalCredit + credit;
			return (total > getMaxCredit(isNewFlag, jobLevel)) ? true : false;
		} else if (currentTime.before(teachTime)
				&& currentTime.get(Calendar.MONTH) != teachTime
						.get(Calendar.MONTH)) {
			hql = "select isNewFlag,jobLevel,nextMonthTotalCredit from Cadre where id=:cadreId";
			Object[] cadreInfo = hibernateDao.findCommonObject(hql,
					Param.getCommonParam("cadreId", cadreId, Type.LONG));
			String isNewFlag = cadreInfo[0].toString();
			String jobLevel = TripletsDESUtil.decrypt(
					systemSettingBo.getStringValue("CURRENT_TRIPLETS_DES_KEY"),
					(String) cadreInfo[1]);
			int nextMonthTotalCredit = (Integer) cadreInfo[2];
			int total = nextMonthTotalCredit + credit;
			return (total > getMaxCredit(isNewFlag, jobLevel)) ? true : false;
		}
		return false;
	}

	// 根据干部是否新任和级别确定可选课程总学时的上限
	private int getMaxCredit(String isNewFlag, String jobLevel) {
		int max = 0;
		if (isNewFlag.equals("1")) {
			max = systemSettingBo.getIntValue("NEW_CADRE_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("1")) {
			max = systemSettingBo.getIntValue("LEVEL_ONE_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("2")) {
			max = systemSettingBo.getIntValue("LEVEL_TWO_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("3")) {
			max = systemSettingBo
					.getIntValue("LEVEL_THREE_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("4")) {
			max = systemSettingBo.getIntValue("LEVEL_FOUR_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("5")) {
			max = systemSettingBo.getIntValue("LEVEL_FIVE_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("6")) {
			max = systemSettingBo.getIntValue("LEVEL_SIX_MAX_CREDIT_OF_MONTH");
		} else if (jobLevel.equals("7")) {
			max = systemSettingBo
					.getIntValue("LEVEL_SEVEN_MAX_CREDIT_OF_MONTH");
		}
		return max;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#isOverMaxStudents(java.lang
	 * .Long)
	 */
	@Override
	@Transactional
	public boolean isOverMaxStudents(Long courseId) {
		String hql = "SELECT classCapacity, classChosenNum FROM Course WHERE id=:id";
		Object[] result = hibernateDao.findCommonObject(hql,
				Param.getCommonParam("id", courseId, Type.LONG));
		return ((Integer) result[1] + 1) > (Integer) result[0] ? true : false;
	}

	@Override
	@Transactional
	public boolean isOverTime(Long courseId) {
		Course course = hibernateDao.get(Course.class, courseId);
		if (course == null) {
			throw new RuntimeException("This course is missing");
		}
		Date endTime = course.getEndTimeChoose();
		Date currentTime = DateUtil.currentTime();
		if (currentTime.before(endTime)) {
			return false;
		} else {
			return true;
		}
	}

	/*
	 * 干部选课 (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#selectCourse4Cadre(java.lang
	 * .Long, java.lang.Long)
	 */
	@Override
	@Transactional
	public void selectCourse4Cadre(Long cadreId, String optUserNum,
			Long courseId) {
		if (isSelected(courseId, cadreId))
			return;

		Course course = hibernateDao.load(Course.class, courseId);
		int credit = course.getCredit();
		Calendar teachTime = Calendar.getInstance();
		teachTime.setTime(course.getTeachTime());
		Calendar currentTime = Calendar.getInstance();
		currentTime.setTime(new Date());

		if (currentTime.before(teachTime)
				&& currentTime.get(Calendar.MONTH) == teachTime
						.get(Calendar.MONTH)) {
			String hql = "update Cadre SET currentMonthTotalCredit=currentMonthTotalCredit+:credit where id=:id";
			hibernateDao.executeUpdate(hql,
					Param.getCommonParam("credit", credit, Type.INTEGER),
					Param.getCommonParam("id", cadreId, Type.LONG));
		} else if (currentTime.before(teachTime)
				&& currentTime.get(Calendar.MONTH) != teachTime
						.get(Calendar.MONTH)) {
			String hql = "update Cadre SET nextMonthTotalCredit=nextMonthTotalCredit+:credit where id=:id";
			hibernateDao.executeUpdate(hql,
					Param.getCommonParam("credit", credit, Type.INTEGER),
					Param.getCommonParam("id", cadreId, Type.LONG));
		}

		CourseSelection cs = new CourseSelection();
		cs.setCadreId(cadreId);
		cs.setCourseId(courseId);
		cs.setSelectCardId(optUserNum);
		cs.setSelectTime(currentTime.getTime());

		hibernateDao.save(cs);

		// String deleteHql =
		// "delete from CourseCanceled where cadreId=:id1 and courseId=:id2";
		// hibernateDao.executeUpdate(deleteHql,
		// Param.getCommonParam("id1", cs.getCadreId(), Type.LONG),
		// Param.getCommonParam("id2", cs.getCourseId(), Type.LONG));

		// 更新课程的选课人数
		String hql = "UPDATE Course SET classChosenNum=(classChosenNum+1) WHERE id=:id";
		hibernateDao.executeUpdate(hql,
				Param.getCommonParam("id", courseId, Type.LONG));
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#queryCoursesForThisStudent
	 * (java.lang.Long)
	 */
	@Override
	@Transactional
	public List<CourseSelectionDto> queryCoursesForThisStudent(Long cadreId) {
		String hql = "FROM CourseSelection WHERE cadreId=:id AND deletedCardNum='0'";
		List<CourseSelection> entities = hibernateDao.findEntities(hql, true,
				Param.getCommonParam("id", cadreId, Type.LONG));
		List<CourseSelectionDto> dtoes = new ArrayList<CourseSelectionDto>(
				entities.size());
		for (CourseSelection entity : entities) {
			dtoes.add(getCourseSelectionDtoFromEntity(entity));
		}
		return dtoes;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#getIdByCardNum(java.lang.String
	 * )
	 */
	@Override
	@Transactional
	public Long getIdByCardNum(String cardNum) {
		String cardNumMD5 = MD5Util.encrypt(cardNum);
		String hql = "SELECT id FROM Cadre WHERE cardNumMD5=:num";
		Long id = hibernateDao.findCommonObject(hql,
				Param.getCommonParam("num", cardNumMD5, Type.STRING));
		if (id == null) {
			return 0L;
		}
		return id;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#cancelCourse(java.lang.Long,
	 * java.lang.String)
	 */
	@Override
	@Transactional
	public void cancelCourse(Long selectedCourseId, String cancelReason) {
		CourseSelection cs = hibernateDao.get(CourseSelection.class,
				selectedCourseId);
		if (cs == null) {
			return;
		}
		CourseCanceled cc = new CourseCanceled();
		cc.setCadreId(cs.getCadreId());
		cc.setCancelCardId(cs.getSelectCardId());
		cc.setCancelTime(new Date());
		cc.setCourseId(cs.getCourseId());
		cc.setCancelReason(cancelReason);

		String hql = "UPDATE Course SET classChosenNum=(classChosenNum-1) WHERE id=:id";
		hibernateDao.executeUpdate(hql,
				Param.getCommonParam("id", cs.getCourseId(), Type.LONG));
		hibernateDao.save(cc);
		hibernateDao.delete(cs);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#
	 * queryStudentsForThisCourseWithCancelInfo(java.lang.Long)
	 */
	@Override
	@Transactional
	public List<CourseCanceledDto> queryStudentsForThisCourseWithCancelInfo(
			Long courseId) {
		String hql = "FROM CourseCanceled WHERE courseId=:id AND deletedCardNum='0'";
		List<CourseCanceled> entities = hibernateDao.findEntities(hql, true,
				Param.getCommonParam("id", courseId, Type.LONG));
		List<CourseCanceledDto> dtoes = new ArrayList<CourseCanceledDto>(
				entities.size());
		for (CourseCanceled entity : entities) {
			dtoes.add(getCourseCanceledDtoFromEntity(entity));
		}
		return dtoes;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#queryCourseSelection(cn.edu
	 * .tsinghua.cic.ccss.qo.CourseSelectionQo, int)
	 */
	@Override
	@Transactional
	public DtoResultWithPageInfo<CourseSelectionDto> queryCourseSelection(
			CourseSelectionQo qo, int page) {
		QueryInfo cadreQi = new QueryInfo(Cadre.class);
		Expression cadreNumEq = Like.like("cardNumMD5", Param
				.getVarcharParamForLike("cardNumMD5", qo.getCardNumMD5(),
						false, MatchType.EXACT));
		Expression cadreNameEq = Like.like("nameMD5", Param
				.getVarcharParamForLike("nameMD5", qo.getCadreNameMD5(), false,
						MatchType.EXACT));
		cadreQi.addExpressionsToWhere(cadreNumEq, cadreNameEq);
		List<Cadre> cadreList = hibernateDao.findEntities(cadreQi);
		Long cadreId[] = null;
		if (!CollectionUtil.isEmpty(cadreList)) {
			cadreId = new Long[cadreList.size()];
			int i = 0;
			for (Cadre cadre : cadreList) {
				cadreId[i] = cadre.getId();
				i++;
			}
		} else {
			return new DtoResultWithPageInfo<CourseSelectionDto>(
					new ArrayList<CourseSelectionDto>(), null);
		}
		QueryInfo courseQi = new QueryInfo(Course.class);
		/*
		 * Expression courseNumEq = Like.like("num",
		 * Param.getDefaultVarcharParamForLike("num", qo.getCourseNum()));
		 */
		Expression courseNameEq = Like
				.like("name",
						Param.getDefaultVarcharParamForLike("name",
								qo.getCourseName()));
		// TODO 新增时间查询功能
		// 判断qo的时间是否为null,否则不能进行时间格式转换
		if (qo.getStart() != null && !"".equals(qo.getStart())
				&& ("".equals(qo.getEnd()) || qo.getEnd() == null)) {
			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Date currtime = DateUtil.currentTime();
			String end = DateUtil.convertDateToString(currtime, "yyyyMMdd");
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime",
							DateUtil.convertStringToDate(end, "yyyyMMdd"),
							Type.DATE));

			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else if (qo.getStart() != null && !"".equals(qo.getStart())) {

			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime", DateUtil.convertStringToDate(
							qo.getEnd(), "yyyyMMdd"), Type.DATE));

			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else {
			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq);
		}

		List<Course> courseList = hibernateDao.findEntities(courseQi);
		Long[] courseId = null;
		if (!CollectionUtil.isEmpty(courseList)) {
			courseId = new Long[courseList.size()];
			int i = 0;
			for (Course course : courseList) {
				courseId[i] = course.getId();
				i++;
			}
		} else {
			return new DtoResultWithPageInfo<CourseSelectionDto>(
					new ArrayList<CourseSelectionDto>(), null);
		}

		QueryInfo csQi = new QueryInfo(CourseSelection.class);
		Expression cadreIdEq = In.in("cadreId",
				Param.getCommonParam("cadreId", cadreId, Type.ARRAY));
		Expression courseIdIn = In.in("courseId",
				Param.getCommonParam("courseId", courseId, Type.ARRAY));
		Expression deleteCardNumEq = Compare.eq("deletedCardNum",
				Param.getCommonParam("deletedCardNum", "0", Type.STRING));
		csQi.addExpressionsToWhere(cadreIdEq, courseIdIn, deleteCardNumEq);
		csQi.setSelect(Select.getCountSelect("id"));
		long total = hibernateDao.getCount(csQi);
		if (total == 0) {
			return new DtoResultWithPageInfo<CourseSelectionDto>(
					new ArrayList<CourseSelectionDto>(), null);
		}
		PageInfo pi = PageInfo.getInstance(page,
				systemSettingBo.getIntValue("DEFAULT_PAGE_SIZE"), total);

		csQi.setPage(true);
		csQi.setFirstResult(pi.getFirstResultNum());
		csQi.setMaxResult(pi.getPageSize());
		csQi.setSelect(null);

		List<CourseSelection> entities = hibernateDao.findEntities(csQi);
		List<CourseSelectionDto> dtoes = new ArrayList<CourseSelectionDto>(
				entities.size());
		for (CourseSelection entity : entities) {
			dtoes.add(getCourseSelectionDtoFromEntity(entity));
		}
		Collections.sort(dtoes);
		return new DtoResultWithPageInfo<CourseSelectionDto>(dtoes, pi);
	}

	@Override
	@Transactional
	public DtoResultWithPageInfo<CourseCanceledDto> queryCourseCanceled(
			CourseSelectionQo qo, int page) {
		QueryInfo cadreQi = new QueryInfo(Cadre.class);
		Expression cadreNumEq = Like.like("cardNumMD5", Param
				.getVarcharParamForLike("cardNumMD5", qo.getCardNumMD5(),
						false, MatchType.EXACT));
		Expression cadreNameEq = Like.like("nameMD5", Param
				.getVarcharParamForLike("nameMD5", qo.getCadreNameMD5(), false,
						MatchType.EXACT));
		cadreQi.addExpressionsToWhere(cadreNumEq, cadreNameEq);
		List<Cadre> cadreList = hibernateDao.findEntities(cadreQi);
		Long cadreId[] = null;
		if (!CollectionUtil.isEmpty(cadreList)) {
			cadreId = new Long[cadreList.size()];
			int i = 0;
			for (Cadre cadre : cadreList) {
				cadreId[i] = cadre.getId();
				i++;
			}
		} else {
			return new DtoResultWithPageInfo<CourseCanceledDto>(
					new ArrayList<CourseCanceledDto>(), null);
		}

		QueryInfo courseQi = new QueryInfo(Course.class);
		/*
		 * Expression courseNumEq = Like.like("num",
		 * Param.getDefaultVarcharParamForLike("num", qo.getCourseNum()));
		 */
		Expression courseNameEq = Like
				.like("name",
						Param.getDefaultVarcharParamForLike("name",
								qo.getCourseName()));
		// TODO 新增时间查询功能
		// 判断qo的时间是否为null,否则不能进行时间格式转换
		if (qo.getStart() != null && !"".equals(qo.getStart())
				&& "".equals(qo.getEnd())) {
			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Date currtime = DateUtil.currentTime();
			String end = DateUtil.convertDateToString(currtime, "yyyyMMdd");
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime",
							DateUtil.convertStringToDate(end, "yyyyMMdd"),
							Type.DATE));

			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else if (qo.getStart() != null && !"".equals(qo.getStart())) {

			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime", DateUtil.convertStringToDate(
							qo.getEnd(), "yyyyMMdd"), Type.DATE));

			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else {
			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq);
		}

		List<Course> courseList = hibernateDao.findEntities(courseQi);
		Long[] courseId = null;
		if (!CollectionUtil.isEmpty(courseList)) {
			courseId = new Long[courseList.size()];
			int i = 0;
			for (Course course : courseList) {
				courseId[i] = course.getId();
				i++;
			}
		} else {
			return new DtoResultWithPageInfo<CourseCanceledDto>(
					new ArrayList<CourseCanceledDto>(), null);
		}

		QueryInfo csQi = new QueryInfo(CourseCanceled.class);
		Expression cadreIdEq = In.in("cadreId",
				Param.getCommonParam("cadreId", cadreId, Type.ARRAY));
		Expression courseIdIn = In.in("courseId",
				Param.getCommonParam("courseId", courseId, Type.ARRAY));
		Expression deleteCardNumEq = Compare.eq("deletedCardNum",
				Param.getCommonParam("deletedCardNum", "0", Type.STRING));
		csQi.addExpressionsToWhere(cadreIdEq, courseIdIn, deleteCardNumEq);
		csQi.setSelect(Select.getCountSelect("id"));
		long total = hibernateDao.getCount(csQi);
		if (total == 0) {
			return new DtoResultWithPageInfo<CourseCanceledDto>(
					new ArrayList<CourseCanceledDto>(), null);
		}
		PageInfo pi = PageInfo.getInstance(page,
				systemSettingBo.getIntValue("DEFAULT_PAGE_SIZE"), total);

		csQi.setPage(true);
		csQi.setFirstResult(pi.getFirstResultNum());
		csQi.setMaxResult(pi.getPageSize());
		csQi.setSelect(null);

		List<CourseCanceled> entities = hibernateDao.findEntities(csQi);
		List<CourseCanceledDto> dtoes = new ArrayList<CourseCanceledDto>(
				entities.size());
		for (CourseCanceled entity : entities) {
			dtoes.add(getCourseCanceledDtoFromEntity(entity));
		}

		return new DtoResultWithPageInfo<CourseCanceledDto>(dtoes, pi);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#isEnableCancelCourse(java.
	 * lang.Long)
	 */
	@Override
	@Transactional
	public boolean isEnableCancelCourse(Long selectedCourseId) {
		CourseSelection cs = hibernateDao.get(CourseSelection.class,
				selectedCourseId);
		if (cs == null) {
			return true;
		}
		Course course = hibernateDao.load(Course.class, cs.getCourseId());
		Date curTime = DateUtil.currentTime();
		Date endTime = course.getEndTimeChoose();
		String state = course.getState();
		// 判断当前的时间是否早于选课结束时间，并且课程状态为可选可退
		if (curTime.before(endTime) && state.equals("1")) {
			return true;
		}
		return false;
	}

	private CourseSelectionDto getCourseSelectionDtoFromEntity(
			CourseSelection entity) {
		CourseDto courseDto = new CourseDto();
		Course course = hibernateDao.load(Course.class, entity.getCourseId());
		BeanPropertyUtil.copy(courseDto, course);
		String hql = "select count(id) from Courseware where courseId=:courseId";
		Param p = Param.getCommonParam("courseId", entity.getCourseId(),
				Type.LONG);
		int wareQ = NumberUtil.convertNumberToTargetClass(
				hibernateDao.getCount(hql, p), Integer.class);
		courseDto.setCoursewareQuantity(wareQ);

		CadreDto cadreDto = new CadreDto();
		Cadre cadre = hibernateDao.load(Cadre.class, entity.getCadreId());
		decrypt(cadreDto, cadre);

		CourseSelectionDto dto = new CourseSelectionDto();

		Date curTime = DateUtil.currentTime();
		Date endTime = course.getEndTimeChoose();
		Date teachTime = course.getTeachTime();
		String state = course.getState();
		// 判断当前的时间是否早于选课结束时间，并且课程状态为可选可退
		if (curTime.before(endTime) && state.equals("1")) {
			dto.setAllowCancel(true);
		} else {
			dto.setAllowCancel(false);
		}
		// 如果是否可以请假的系统参数为0，或者已经请假，则不能请假
		if (systemSettingBo.getStringValue("ALLOW_LEAVE").equals("0")
				|| entity.getState().equals("1") || teachTime.before(curTime)
				|| course.getEndTimeChoose().after(curTime)) {
			dto.setAllowLeave(false);
		} else {
			dto.setAllowLeave(true);
		}

		BeanPropertyUtil.copy(dto, entity);

		dto.setCadreDto(cadreDto);
		dto.setCourseDto(courseDto);

		return dto;
	}

	private CourseCanceledDto getCourseCanceledDtoFromEntity(
			CourseCanceled entity) {
		CourseDto courseDto = new CourseDto();
		Course course = hibernateDao.load(Course.class, entity.getCourseId());
		BeanPropertyUtil.copy(courseDto, course);

		CadreDto cadreDto = new CadreDto();
		Cadre cadre = hibernateDao.load(Cadre.class, entity.getCadreId());
		decrypt(cadreDto, cadre);

		CourseCanceledDto dto = new CourseCanceledDto();
		BeanPropertyUtil.copy(dto, entity);

		dto.setCadreDto(cadreDto);
		dto.setCourseDto(courseDto);

		return dto;
	}

	// 解密实体对象为DTO对象
	private <DTO extends SuperCadreDto> DTO decrypt(DTO dto, Cadre entity) {
		String key = systemSettingBo.getStringValue("CURRENT_TRIPLETS_DES_KEY");

		BeanPropertyUtil.copy(dto, entity);
		if (StringUtil.hasText(dto.getCardNum()))
			dto.setCardNum(TripletsDESUtil.decrypt(key, dto.getCardNum()));
		if (StringUtil.hasText(dto.getEmail()))
			dto.setEmail(TripletsDESUtil.decrypt(key, dto.getEmail()));
		if (StringUtil.hasText(dto.getJobLevel()))
			dto.setJobLevel(TripletsDESUtil.decrypt(key, dto.getJobLevel()));
		if (StringUtil.hasText(dto.getMobileNum()))
			dto.setMobileNum(TripletsDESUtil.decrypt(key, dto.getMobileNum()));
		if (StringUtil.hasText(dto.getName()))
			dto.setName(TripletsDESUtil.decrypt(key, dto.getName()));
		if (StringUtil.hasText(dto.getOfficeTel()))
			dto.setOfficeTel(TripletsDESUtil.decrypt(key, dto.getOfficeTel()));
		if (StringUtil.hasText(dto.getUnitName()))
			dto.setUnitName(TripletsDESUtil.decrypt(key, dto.getUnitName()));

		return dto;
	}

	private boolean isSelected(Long courseId, Long cadreId) {
		Long quantity = hibernateDao
				.findCommonObject(
						"select count(id) from CourseSelection where courseId=:courseId and cadreId=:cadreId AND deletedCardNum='0'",
						Param.getCommonParam("courseId", courseId, Type.LONG),
						Param.getCommonParam("cadreId", cadreId, Type.LONG));
		return (quantity > 0) ? true : false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#leave4Admin(java.lang.Long,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	@Transactional
	public void leave4Admin(Long selectedCourseId, String state,
			String leaveReason) {
		CourseSelection cs = hibernateDao.load(CourseSelection.class,
				selectedCourseId);
		cs.setState(state);
		cs.setStateExplanation(leaveReason);
		hibernateDao.update(cs);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.edu.tsinghua.cic.ccss.bo.CourseSelectBo#leave4Cadre(java.lang.Long,
	 * java.lang.String)
	 */
	@Override
	@Transactional
	public void leave4Cadre(Long selectedCourseId, String leaveReason) {
		CourseSelection cs = hibernateDao.load(CourseSelection.class,
				selectedCourseId);
		cs.setState("1");
		cs.setStateExplanation(leaveReason);
		hibernateDao.update(cs);
	}

	@Override
	@Transactional
	public List<SelectedCourseDto4Export> export(CourseSelectionQo qo) {

		/** 查询课程 */
		QueryInfo courseQi = new QueryInfo(Course.class);
		Expression courseNameEq = Like
				.like("name",
						Param.getDefaultVarcharParamForLike("name",
								qo.getCourseName()));
		// 判断qo的时间是否为null,否则不能进行时间格式转换
		if (qo.getStart() != null && !"".equals(qo.getStart())
				&& "".equals(qo.getEnd())) {
			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Date currtime = DateUtil.currentTime();
			String end = DateUtil.convertDateToString(currtime, "yyyyMMdd");
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime",
							DateUtil.convertStringToDate(end, "yyyyMMdd"),
							Type.DATE));
			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else if (qo.getStart() != null && !"".equals(qo.getStart())) {
			Expression courseStarttime = Compare.gt("teachTime", Param
					.getCommonParam("start", DateUtil.convertStringToDate(
							qo.getStart(), "yyyyMMdd"), Type.DATE));
			Expression courseEndtime = Compare.lt("teachTime", Param
					.getCommonParam("teachTime", DateUtil.convertStringToDate(
							qo.getEnd(), "yyyyMMdd"), Type.DATE));
			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq,
					courseStarttime, courseEndtime);
		} else {
			courseQi.addExpressionsToWhere(/* courseNumEq, */courseNameEq);
		}
		List<Course> courseList = hibernateDao.findEntities(courseQi);
		Long[] courseId = null;
		if (!CollectionUtil.isEmpty(courseList)) {
			courseId = new Long[courseList.size()];
			int i = 0;
			for (Course course : courseList) {
				courseId[i] = course.getId();
				i++;
			}
		} else {
			return new ArrayList<SelectedCourseDto4Export>();
		}

		/** 查询干部 */
		QueryInfo cadreQi = new QueryInfo(Cadre.class);
		Expression cadreNumEq = Like.like("cardNumMD5", Param
				.getVarcharParamForLike("cardNumMD5", qo.getCardNumMD5(),
						false, MatchType.EXACT));
		Expression cadreNameEq = Like.like("nameMD5", Param
				.getVarcharParamForLike("nameMD5", qo.getCadreNameMD5(), false,
						MatchType.EXACT));
		cadreQi.addExpressionsToWhere(cadreNumEq, cadreNameEq);
		List<Cadre> cadreList = hibernateDao.findEntities(cadreQi);
		Long cadreId[] = null;
		if (!CollectionUtil.isEmpty(cadreList)) {
			cadreId = new Long[cadreList.size()];
			int i = 0;
			for (Cadre cadre : cadreList) {
				cadreId[i] = cadre.getId();
				i++;
			}
		} else {
			return new ArrayList<SelectedCourseDto4Export>();
		}

		/** 选课记录dto */
		QueryInfo csQi = new QueryInfo(CourseSelection.class);
		Expression cadreIdEq = In.in("cadreId",
				Param.getCommonParam("cadreId", cadreId, Type.ARRAY));
		Expression courseIdIn = In.in("courseId",
				Param.getCommonParam("courseId", courseId, Type.ARRAY));
		Expression deleteCardNumEq = Compare.eq("deletedCardNum",
				Param.getCommonParam("deletedCardNum", "0", Type.STRING));
		csQi.addExpressionsToWhere(cadreIdEq, courseIdIn, deleteCardNumEq);

		List<CourseSelection> entities = hibernateDao.findEntities(csQi);
		List<CourseSelectionDto> dtoes = new ArrayList<CourseSelectionDto>(
				entities.size());
		for (CourseSelection entity : entities) {
			dtoes.add(getCourseSelectionDtoFromEntity(entity));
		}
		Collections.sort(dtoes);

		/** 导出dto */
		List<SelectedCourseDto4Export> result = new ArrayList<SelectedCourseDto4Export>(
				entities.size());
		for (CourseSelectionDto dto : dtoes) {
			SelectedCourseDto4Export dto4Export = new SelectedCourseDto4Export();
			dto4Export.setCadreName(dto.getCadreDto().getName());
			dto4Export.setCardNum(dto.getCadreDto().getCardNum());
			dto4Export.setCourseName(dto.getCourseDto().getName());
			dto4Export.setJobLevel(dto.getCadreDto().getJobLevel());
			dto4Export.setNum(dto.getCourseDto().getNum());
			dto4Export.setUnitName(dto.getCadreDto().getUnitName());
			result.add(dto4Export);
		}
		return result;
	}

	@Override
	@Transactional
	public List<CourseDto> queryCourse4Download(CourseSelectCadreQo qo, int days) {
		QueryInfo qi = new QueryInfo(Course.class);
		Expression nameLike = Like.like("name",
				Param.getDefaultVarcharParamForLike("name", qo.getName()));
		Expression teacherLike = Like.like(
				"teacherName",
				Param.getDefaultVarcharParamForLike("teacherName",
						qo.getTeacherName()));

		Date currTime = DateUtil.currentTime();
		String endTime = DateUtil.convertDateToString(currTime, "yyyyMMdd");
		String startTime = DateUtil.convertDateToString(DateUtil.calculateDate(
				currTime, 0 - days, Calendar.DAY_OF_MONTH), "yyyyMMdd");

		if (StringUtil.hasText(qo.getStart())) {
			startTime = qo.getStart();
			if (StringUtil.hasText(qo.getEnd())) {
				endTime = qo.getEnd();
			}
		}

		Param endTimeParam = Param.getCommonParam("endTime", endTime + "2359",
				Type.STRING);
		endTimeParam.setPrefix("to_date(");
		endTimeParam.setPostfix(",'yyyyMMddHH24MI')");
		Param startParam = Param.getCommonParam("startTime",
				startTime + "0000", Type.STRING);
		startParam.setPrefix("to_date(");
		startParam.setPostfix(",'yyyyMMddHH24MI')");
		Between betweenTime = Between.between("teachTime", startParam,
				endTimeParam);

		qi.addExpressionsToWhere(nameLike, teacherLike, betweenTime);
		qi.setOrderby(OrderBy.getOrderBy(Order.desc("teachTime")));

		List<Course> entities = hibernateDao.findEntities(qi);
		List<CourseDto> dtos = new ArrayList<CourseDto>(entities.size());
		for (Course entity : entities) {
			String hql = "select count(id) from Courseware where courseId=:courseId";
			Param p = Param.getCommonParam("courseId", entity.getId(),
					Type.LONG);
			int wareQ = NumberUtil.convertNumberToTargetClass(
					hibernateDao.getCount(hql, p), Integer.class);
			CourseDto dto = new CourseDto();
			BeanPropertyUtil.copy(dto, entity);
			dto.setCoursewareQuantity(wareQ);
			dtos.add(dto);
		}
		return dtos;
	}
	
	@Override
	@Transactional
	public List<MsgResultOfChekInDto> findMsgResultOfCheckInByQo(
			MsgResultOfChekInQo qo) {
		
		List<Cadre> cadreList = hibernateDao.findEntities(getCadreQi(qo));
		Long cadreId[] = null;
		if (!CollectionUtil.isEmpty(cadreList)) {
			cadreId = new Long[cadreList.size()];
			int i = 0;
			for (Cadre cadre : cadreList) {
				cadreId[i] = cadre.getId();
				i++;
			}
		} else {
			return new ArrayList<MsgResultOfChekInDto>();
		}
		
		QueryInfo qi = new QueryInfo(MsgResultOfChekIn.class);
		// 短信接收时间
		Between betweenTime = null;
		if (qo.getStart() != null && !"".equals(qo.getStart())
				&& "".equals(qo.getEnd())) {
			Param startParam = Param.getCommonParam("start", qo.getStart() + "0000",
					Type.STRING);
			Param endParam = Param.getCommonParam("end",
					DateUtil.convertDateToString(DateUtil.currentTime(),
							"yyyyMMdd2359"), Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMddHH24MI')");
			endParam.setPostfix(",'yyyyMMddHH24MI')");
			betweenTime = Between.between("receiptTime", startParam, endParam);
		} else if (qo.getStart() != null && !"".equals(qo.getStart())) {

			Param startParam = Param.getCommonParam("start", qo.getStart() + " 00:00",
					Type.STRING);
			Param endParam = Param.getCommonParam("end", qo.getEnd() + " 23:59",
					Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMdd HH24:MI')");
			endParam.setPostfix(",'yyyyMMdd HH24:MI')");
			betweenTime = Between.between("receiptTime", startParam, endParam);
		}
		if (betweenTime != null) {
			qi.addExpressionsToWhere(betweenTime);
		}
		Expression cadreIdEq = In.in("cadreId",
				Param.getCommonParam("cadreId", cadreId, Type.ARRAY));
		qi.addExpressionsToWhere(cadreIdEq);
		List<MsgResultOfChekIn> entities = hibernateDao.findEntities(qi);
		
		if(entities.size() ==0) {
			return new ArrayList<MsgResultOfChekInDto>();
		}
		
		List<MsgResultOfChekInDto> dtos = new ArrayList<MsgResultOfChekInDto>(entities.size());
		for (MsgResultOfChekIn entity : entities) {
			dtos.add(getMsgResultOfCheckInDtoFromEntity(entity));
		}
		return dtos;
	}

	private MsgResultOfChekInDto getMsgResultOfCheckInDtoFromEntity(MsgResultOfChekIn entity) {
		
		MsgResultOfChekInDto dto = new MsgResultOfChekInDto();
		CadreDto cadreDto = new CadreDto();
		Cadre cadre = hibernateDao.load(Cadre.class, entity.getCadreId());
		decrypt(cadreDto, cadre);
		BeanPropertyUtil.copy(dto, entity);
		dto.setCadreDto(cadreDto);
		return dto;
	}
	
	/**
	 *  查询条件为干部姓名
	 *  @param MsgResultOfChekInQo
	 * */
	private QueryInfo getCadreQi(MsgResultOfChekInQo qo){
		QueryInfo cadreQi = new QueryInfo(Cadre.class);
		/*Expression cadreNumEq = Like.like("cardNumMD5", Param
				.getVarcharParamForLike("cardNumMD5", qo.getCardNumMD5(),
						false, MatchType.EXACT));*/
		Expression cadreNameEq = Like.like("nameMD5", Param
				.getVarcharParamForLike("nameMD5", qo.getCadreNameMD5(), false,
						MatchType.EXACT));
		cadreQi.addExpressionsToWhere(/*cadreNumEq,*/ cadreNameEq);
		
		return cadreQi;
	}
	
	/**
	 * 查询条件为时间
	 * @param MsgResultOfChekInQo
	 * 
	 * */
	private QueryInfo getCourseQi(MsgResultOfChekInQo qo){
		
		QueryInfo courseQi = new QueryInfo(Course.class);
		
		Expression courseNameExp = Like.like("name", 
				Param.getDefaultVarcharParamForLike("courseName", qo.getCourseName()));
		
		Between betweenTime = null;
		if (qo.getStart() != null && !"".equals(qo.getStart())
				&& ("".equals(qo.getEnd()) || qo.getEnd() == null)) {
			Param startParam = Param.getCommonParam("start", qo.getStart() + "0000",
					Type.STRING);
			Param endParam = Param.getCommonParam("end",
					DateUtil.convertDateToString(DateUtil.currentTime(),
							"yyyyMMdd2359"), Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMddHH24MI')");
			endParam.setPostfix(",'yyyyMMddHH24MI')");
			betweenTime = Between.between("teachTime", startParam, endParam);
		} else if (qo.getStart() != null && !"".equals(qo.getStart())) {

			Param startParam = Param.getCommonParam("start", qo.getStart() + " 00:00",
					Type.STRING);
			Param endParam = Param.getCommonParam("end", qo.getEnd() + " 23:59",
					Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMdd HH24:MI')");
			endParam.setPostfix(",'yyyyMMdd HH24:MI')");
			betweenTime = Between.between("teachTime", startParam, endParam);
		}
		if (betweenTime != null) {
			courseQi.addExpressionsToWhere(betweenTime,courseNameExp);
		} else {
			courseQi.addExpressionsToWhere(courseNameExp);
		}
		
		return courseQi;
	}
	
	@Override
	@Transactional
	public List<CourseSelectionDto> findCourseSelection4CheckIn(
			MsgResultOfChekInQo qo) {
		
		List<Cadre> cadreList = hibernateDao.findEntities(getCadreQi(qo));
		Long cadreId[] = null;
		if (!CollectionUtil.isEmpty(cadreList)) {
			cadreId = new Long[cadreList.size()];
			int i = 0;
			for (Cadre cadre : cadreList) {
				cadreId[i] = cadre.getId();
				i++;
			}
		} else {
			return new ArrayList<CourseSelectionDto>();
		}
		
		List<Course> courseList = hibernateDao.findEntities(getCourseQi(qo));
		Long[] courseId = null;
		if (!CollectionUtil.isEmpty(courseList)) {
			courseId = new Long[courseList.size()];
			int i = 0;
			for (Course course : courseList) {
				courseId[i] = course.getId();
				i++;
			}
		} else {
			return new ArrayList<CourseSelectionDto>();
		}

		QueryInfo csQi = new QueryInfo(CourseSelection.class);
		Expression cadreIdEq = In.in("cadreId",
				Param.getCommonParam("cadreId", cadreId, Type.ARRAY));
		Expression courseIdIn = In.in("courseId",
				Param.getCommonParam("courseId", courseId, Type.ARRAY));
		Expression deleteCardNumEq = Compare.eq("deletedCardNum",
				Param.getCommonParam("deletedCardNum", "0", Type.STRING));
		csQi.addExpressionsToWhere(cadreIdEq, courseIdIn, deleteCardNumEq);
		List<CourseSelection> entities = hibernateDao.findEntities(csQi);
		List<CourseSelectionDto> dtoes = new ArrayList<CourseSelectionDto>(
				entities.size());
		for (CourseSelection entity : entities) {
			dtoes.add(getCourseSelectionDtoFromEntity(entity));
		}
		
		return dtoes;
	}
}
